package com.training.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.training.dto.Employee;

public class EmployeeDAO {

	public Employee validateEmployeeLogin(String userName, String password) {

		Employee employee = null;

		// select * from employee_information, 
		//emp_payroll where employee_information.emp_id= emp_payroll.emp.id and 
		//employee_information.email='test@test.com'; 
		
		String SQL_QUERY = "Select * from employee_info where emp_id='"
				+ userName + "'  and password='" + password + "'";

		try {
			// Load the Driver - Register Driver
			Class.forName("com.mysql.jdbc.Driver");
			// Getting the Connection
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/employee", "root", "root");
			// Create Statment ....
			Statement statement = connection.createStatement();
			// Execute the query on the statment
			ResultSet resultSet = statement.executeQuery(SQL_QUERY);

			while (resultSet.next()) {
				employee = new Employee();
				employee.setEmpId(resultSet.getString("emp_id"));
				employee.setPassword(resultSet.getString("password"));
				employee.setFirstName(resultSet.getString("emp_first_name"));
				employee.setLastName(resultSet.getString("emp_last_name"));

			}

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return employee;

	}

	public Employee searchByEmployeeID(String employeeId)
			throws ClassNotFoundException, SQLException {

		Employee employee = null;
		String search_query = "select * FROM EMPLOYEE_INFO WHERE EMP_ID = ?";

		// Load the Driver - Register Driver
		Class.forName("com.mysql.jdbc.Driver");
		// Getting the Connection
		Connection connection = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/employee", "root", "root");

		PreparedStatement preparedStatement = connection
				.prepareStatement(search_query);
		 
 
		preparedStatement.setString(1, employeeId);
		

		ResultSet resultSet = preparedStatement.executeQuery();

		while (resultSet.next()) {
			employee = new Employee();
			/*employee.setEmpId(resultSet.getString(1));
			employee.setPassword(resultSet.getString(2));
			employee.setFirstName(resultSet.getString(3));
			employee.setLastName(resultSet.getString(4));*/
			
			employee.setEmpId(resultSet.getString("emp_id"));
			employee.setPassword(resultSet.getString("password"));
			employee.setFirstName(resultSet.getString("emp_first_name"));
			employee.setLastName(resultSet.getString("emp_last_name"));

		}

		return employee;

	}

	
	
	public List<Employee> searchByEmployeeName(String employeeName)
			throws ClassNotFoundException, SQLException {

		Employee employee = null;
		List<Employee> employeeList = new ArrayList<Employee>();
		
		String search_query = "select * FROM EMPLOYEE_INFO WHERE EMP_FIRST_NAME LIKE ?  OR EMP_LAST_NAME LIKE ? ";

		// Load the Driver - Register Driver
		Class.forName("com.mysql.jdbc.Driver");
		// Getting the Connection
		Connection connection = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/employee", "root", "root");

		PreparedStatement preparedStatement = connection
				.prepareStatement(search_query);
		 
 
		preparedStatement.setString(1, "%"+employeeName+"%");
		preparedStatement.setString(2, "%"+employeeName+"%");
		

		ResultSet resultSet = preparedStatement.executeQuery();

		while (resultSet.next()) {
			employee = new Employee();
			/*employee.setEmpId(resultSet.getString(1));
			employee.setPassword(resultSet.getString(2));
			employee.setFirstName(resultSet.getString(3));
			employee.setLastName(resultSet.getString(4));*/
			
			employee.setEmpId(resultSet.getString("emp_id"));
			employee.setPassword(resultSet.getString("password"));
			employee.setFirstName(resultSet.getString("emp_first_name"));
			employee.setLastName(resultSet.getString("emp_last_name"));
			employeeList.add(employee);

		}

		return employeeList;

	}

	
	
	
	public int registerEmployee(Employee employee) {
		String SQL_QUERY = "INSERT INTO employee_info (emp_id,PASSWORD,emp_last_name,emp_first_name ) "
				+ "VALUES('"
				+ employee.getEmpId()
				+ "','"
				+ employee.getPassword()
				+ "','"
				+ employee.getLastName()
				+ "','" + employee.getFirstName() + "')";
		try {
			// Load the Driver - Register Driver
			Class.forName("com.mysql.jdbc.Driver");
			// Getting the Connection
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/employee", "root", "root");
			// Create Statment ....
			Statement statement = connection.createStatement();
			// Execute the query on the statment
			return statement.executeUpdate(SQL_QUERY);

		} catch (Exception e) {

		}
		return 0;

	}

	public static void main(String[] args) {

		Employee employee = new EmployeeDAO().validateEmployeeLogin("emp1",
				"password");

		System.out.println(employee.getFirstName());

	}

}
